--SELECT * FROM FN_GET_ALL_FORECAST_SALES_PERSONS_IDS ()
/**
	AUTHOR		: S.SUJAN
	DATE		: 15th SEPT 2005
	DESCRIPTION	: TO GET ALL THE ACTIVE SALES PERSONS IDS ASSIGNED FOR THE FORECAST.
			  THE RESULT SET RETURNED WILL BE IN THE FORMAT [<VP_SPERON_ID1>,<REGIONAL_SPERON_ID1>, <REGIONAL_SPERON_ID2>...,<VP_SPERON_ID2>,<REGIONAL_SPERON_ID1>, <REGIONAL_SPERON_ID2>...,<REST_OF_SPEROSN_IDS>]
**/
CREATE FUNCTION FN_GET_ALL_FORECAST_SALES_PERSONS_IDS ()
RETURNS @RESULTS TABLE ( SPERSON_ID INT )
AS
BEGIN
	DECLARE @VP_SPERSONS_CURSOR CURSOR
	DECLARE @REGIONAL_SPERSONS_CURSOR CURSOR
	DECLARE @VP_LEVEL_NO 		INT
	DECLARE @VP_SPERSON_ID 		INT
	DECLARE @REGIONAL_SPERSON_ID 	INT
	SELECT TOP 1 @VP_LEVEL_NO = LevelOrder FROM FCLEVEL WHERE LevelName = 'VP OF SALES'
	SET @VP_SPERSONS_CURSOR = CURSOR FOR SELECT DISTINCT SPERSONID FROM FCSPERSONREL, SALES_PERSONS WHERE SPERSONID = SalesPerson_Id AND Deactivated = 0 AND LEVELORDER = @VP_LEVEL_NO
	
	OPEN @VP_SPERSONS_CURSOR
	FETCH NEXT FROM @VP_SPERSONS_CURSOR INTO @VP_SPERSON_ID
	WHILE @@FETCH_STATUS = 0
	 BEGIN
		INSERT INTO @RESULTS ( SPERSON_ID ) VALUES ( @VP_SPERSON_ID )
		SET @REGIONAL_SPERSONS_CURSOR = CURSOR FOR SELECT DISTINCT SPERSONID FROM FCSPERSONREL, SALES_PERSONS WHERE SPERSONID = SalesPerson_Id AND Deactivated = 0 AND MGRID = @VP_SPERSON_ID
		OPEN @REGIONAL_SPERSONS_CURSOR 
		FETCH NEXT FROM @REGIONAL_SPERSONS_CURSOR INTO @REGIONAL_SPERSON_ID
		WHILE @@FETCH_STATUS = 0
		 BEGIN
			INSERT INTO @RESULTS ( SPERSON_ID ) VALUES ( @REGIONAL_SPERSON_ID )
			SET @REGIONAL_SPERSON_ID = NULL
			FETCH NEXT FROM @REGIONAL_SPERSONS_CURSOR INTO @REGIONAL_SPERSON_ID
		 END
		CLOSE @REGIONAL_SPERSONS_CURSOR
		DEALLOCATE @REGIONAL_SPERSONS_CURSOR
		SET @VP_SPERSON_ID = NULL
		FETCH NEXT FROM @VP_SPERSONS_CURSOR INTO @VP_SPERSON_ID
	 END
	CLOSE @VP_SPERSONS_CURSOR
	DEALLOCATE @VP_SPERSONS_CURSOR
	INSERT INTO @RESULTS ( SPERSON_ID ) SELECT DISTINCT SPERSONID FROM FCSPERSONREL, SALES_PERSONS WHERE SPERSONID = SalesPerson_Id AND Deactivated = 0 AND SPERSONID NOT IN ( SELECT SPERSON_ID FROM @RESULTS )
	RETURN
END